package output;

import java.sql.*;

import forum.ForumItem;
import forum.ForumPost;
import forum.ForumSentence;
import forum.ForumTag;
import forum.ForumTopic;

public class RelationDatabase extends DatabaseOutput {
	private Connection conn;
	private PreparedStatement stmt;

	/**
	 * Create the database interface object. Connection to the database is
	 * performed later.
	 * 
	 * @throws Exception
	 */
	public RelationDatabase(String dbName, String userName, String password)
			throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(dbName, userName, password);
//		this.dropTables();
		this.createTables();
	}

	public void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
		}
		conn = null;
	}

	/**
	 * Method that does _not_ create tables, but at least print them out.
	 * 
	 * @throws SQLException
	 */
	public void createTables() throws SQLException {
		String createSchema = "CREATE SCHEMA IF NOT EXISTS `asa`; ";
		String databaseToUse = "USE `asa`;";

		String createPost = "CREATE TABLE IF NOT EXISTS `post`("
				+ "`postId` int(4) NOT NULL, "
				+ "`source` varchar(255), "
				+ "`date` date, "
				+ "`time` varchar(8), "
				+ "`title` varchar(255), "
				+ "`poster` varchar(255), "
				+ "`link` varchar(255), "
				+ "`categoryName` varchar(255), "
				+ "PRIMARY KEY (`postId`)"
				+ ");";

		String createTag = "CREATE TABLE IF NOT EXISTS `tag` ("
				+ "`tagId` int(4), " + "`tagName` varchar(255), "
				+ "PRIMARY KEY (`tagId`)" + ");";

		String createTagsInPost = "CREATE TABLE IF NOT EXISTS `tagpost` ("
				+ "`postId` int(4), " + "`tagId` int(4), "
				+ "PRIMARY KEY (`postId`, `tagId`), "
				+ "FOREIGN KEY (`postId`) REFERENCES `post` (`postId`), "
				+ "FOREIGN KEY (`tagId`) REFERENCES `tag` (`tagId`)" + ");";

		String createTopic = "CREATE TABLE IF NOT EXISTS `topic` ("
				+ "`topicId` int(4) NOT NULL," + "`topicName` varchar(255),"
				+ "PRIMARY KEY (`topicId`)" + ");";

		String createSentence = "CREATE TABLE IF NOT EXISTS `sentence` ("
				+ "`sentenceId` int(4) NOT NULL," + "`text` varchar(4096),"
				+ "`itemsText` varchar(4096),`emotion` varchar(255),"
				+ "`postId` int(4)," + "PRIMARY KEY (`sentenceId`),"
				+ "FOREIGN KEY (`postId`) REFERENCES `post`(`postId`)" + ");";

		String createItem = "CREATE TABLE IF NOT EXISTS `item` ("
				+ "`itemId` int(4) NOT NULL," + "`itemName` varchar(255),"
				+ "`topicId` int(4)," + "PRIMARY KEY (`itemId`),"
				+ "FOREIGN KEY (`topicId`) REFERENCES `topic`(`topicId`)"
				+ ");";

		String createSentenceItem = "CREATE TABLE IF NOT EXISTS `sentenceitem` ("
				+ "`sentenceId` int(4),"
				+ "`itemId` int(4),"
				+ "FOREIGN KEY (`sentenceId`) REFERENCES `sentence`(`sentenceId`),"
				+ "FOREIGN KEY (`itemId`) REFERENCES `item`(`itemId`),"
				+ "PRIMARY KEY (`sentenceId`, `itemId`)" + ");";

		Statement ztmt = conn.createStatement();
		ztmt.executeUpdate(createSchema);
		ztmt.executeUpdate(databaseToUse);
		ztmt.executeUpdate(createPost);
		ztmt.executeUpdate(createTag);
		ztmt.executeUpdate(createTagsInPost);
		ztmt.executeUpdate(createTopic);
		ztmt.executeUpdate(createSentence);
		ztmt.executeUpdate(createItem);
		ztmt.executeUpdate(createSentenceItem);
	}

	public void dropTables() throws SQLException {
		String dropSchema = "DROP SCHEMA IF EXISTS `asa`;";

		Statement ztmt = conn.createStatement();
		ztmt.executeUpdate(dropSchema);
	}

	@Override
	public void close() {
		this.closeConnection();
	}

	@Override
	public void insertSentenceItem(ForumSentence sent, ForumItem item)
			throws SQLException {
		String sql = "INSERT INTO `sentenceItem` (`sentenceId`, `itemId`) "
				+ "VALUES (?, ?)";
		prepare(sql);
		stmt.setInt(1, sent.getSentId());
		stmt.setInt(2, item.getItemId());
		stmt.executeUpdate();
		stmt.close();
	}

	@Override
	public void insertItem(ForumItem item) throws SQLException {
		String sql = "INSERT INTO `item` (`itemId`, `itemName`, `topicId`) "
				+ "VALUES (?, ?, ?)";
		prepare(sql);
		stmt.setInt(1, item.getItemId());
		stmt.setString(2, item.getItemName());
		stmt.setInt(3, item.getTopic().getTopicId());
		stmt.executeUpdate();
		stmt.close();
	}

	@Override
	public void insertSentence(ForumSentence sent) throws SQLException {
		String sql = "INSERT INTO `sentence` (`sentenceId`, `text`, `itemsText`, `emotion`, `postId`) "
				+ "VALUES (?, ?, ?, ?, ?)";
		prepare(sql);
		stmt.setInt(1, sent.getSentId());
		stmt.setString(2, sent.getText());
		stmt.setString(3, sent.getItemsText());
		stmt.setString(4, sent.getEmotion());
		stmt.setInt(5, sent.getPostId());
		stmt.executeUpdate();
		stmt.close();
	}

	@Override
	public void insertTopic(ForumTopic topic) throws SQLException {
		String sql = "INSERT INTO `topic` (`topicId`, `topicName`) "
				+ "VALUES (?, ?)";
		prepare(sql);
		stmt.setInt(1, topic.getTopicId());
		stmt.setString(2, topic.getTopicName());
		stmt.executeUpdate();
		stmt.close();
	}

	@Override
	public void insertPost(ForumPost post) throws SQLException {
		String sql = "INSERT INTO `post` (`postId`, `source`, `date`, `time`, `title`, `poster`, `link`, `categoryName`) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
		prepare(sql);
		stmt.setInt(1, post.getPostId());
		stmt.setString(2, post.getSource());
		stmt.setString(3, post.getPostDate());
		stmt.setString(4, post.getPostTime());
		stmt.setString(5, post.getTitle());
		stmt.setString(6, post.getPoster());
		stmt.setString(7, post.getHyperlink());
		stmt.setString(8, post.getForumCategory());
		stmt.executeUpdate();
		stmt.close();
	}

	@Override
	public void insertTag(ForumTag tag) throws SQLException {
		String sql = "INSERT INTO `tag` (`tagId`, `tagName`) VALUES (?, ?)";
		prepare(sql);
		stmt.setInt(1, tag.getTagId());
		stmt.setString(2, tag.getTagName());
		stmt.executeUpdate();
		stmt.close();
	}

	@Override
	public void insertTagsInPost(ForumPost post, ForumTag tag) throws Exception {
		String sql = "INSERT INTO `tagpost` (`postId`, `tagId`) "
				+ "VALUES (?, ?)";
		prepare(sql);
		stmt.setInt(1, post.getPostId());
		stmt.setInt(2, tag.getTagId());
	}

	private void prepare(String sql) throws SQLException {
		stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
	}

}
